IF exists(SELECT * FROM dbo.sysobjects WHERE name='Fl_vulToetsVakKenmerken' AND xtype='P') 
BEGIN
	DROP PROCEDURE Fl_vulToetsVakKenmerken
END
GO

CREATE         procedure Fl_vulToetsVakKenmerken
as   
insert into Fl_toetsVakKenmerken(klas,vak,semester)
select distinct deKlas,hetVak,1 from leraars..rooster as r
   left join Fl_toetsVakKenmerken  as k
   on k.klas = r.deKlas and k.vak = r.hetVak
   where left(r.deKlas,1) = '6'
	and (k.klas is null) 
	and (k.vak is null)

insert into Fl_toetsVakKenmerken(klas,vak,semester)
select distinct deKlas,hetVak,2 from leraars..rooster as r
   left join  (select * from Fl_toetsVakKenmerken 
				where semester <> 1) as k
   	on k.klas = r.deKlas and k.vak = r.hetVak
   where left(r.deKlas,1) = '6' 
	and (k.klas is null) 
	and (k.vak is null)

insert into Fl_toetsVakKenmerken(klas,vak,semester)
select distinct extra.klas,extra.vak,1 from 
	(select distinct k.klas,r.vak,1 as semester from fl_extraVakken as r
   		cross join Fl_toetsVakKenmerken  as k) as extra
   left join Fl_toetsVakKenmerken  as t
   on t.klas = extra.klas and t.vak = extra.vak
   where left(extra.klas,1) = '6'
	and (t.klas is null) 
	and (t.vak is null)

insert into Fl_toetsVakKenmerken(klas,vak,semester)
select distinct extra.klas,extra.vak,2 from 
	(select distinct k.klas,r.vak,2 as semester from fl_extraVakken as r
   		cross join Fl_toetsVakKenmerken  as k) as extra
   left join (select * from Fl_toetsVakKenmerken 
				where semester <> 1)  as t
   on t.klas = extra.klas and t.vak = extra.vak
   where left(extra.klas,1) = '6'
	and (t.klas is null) 
	and (t.vak is null)

update Fl_toetsVakKenmerken
set lokaalToets = r.lokaalToets
from  Fl_toetsVakKenmerken  as k
   	left join Fl_klaslokalen as r
   on k.klas = r.klas